package server;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class BankFAccList {
	private Data data;
	
	public BankFAccList(){
		data=new Data();
	}
	
	public int getLastFANum(int pID){
		String sql;
		ResultSet rs=null;
		int lastFANum=0;
		
		sql="select * from facclist where pID="+pID+" order by faNum";
		rs=data.getResultSet(sql);
		try {
			while(rs.next()){
				lastFANum=rs.getInt("faNum");
		}
			} catch (SQLException e) {
		
			e.printStackTrace();
			}
			
		return lastFANum;
	}
	
	public int getFirstFANum(int pID){
		String sql;
		ResultSet rs=null;
		int firstFANum=-1;
		
		sql="select * from facclist where pID="+pID+" order by faNum";
		rs=data.getResultSet(sql);
		try {
			if(rs.next()){
				firstFANum=rs.getInt("faNum");
		}
			} catch (SQLException e) {
		
			e.printStackTrace();
			}
			
		return firstFANum;
	}
	
	public int getNextFANum(int pID,int faNum){
		String sql;
		ResultSet rs=null;
		int nextFANum=-1;
		
		sql="select * from facclist where pID="+pID+" order by faNum";
		rs=data.getResultSet(sql);
		try {
			while(rs.next()){
				if(rs.getInt("faNum")==faNum){
					rs.next();
					nextFANum=rs.getInt("faNum");
				}
			}
		} catch (SQLException e) {
		
			e.printStackTrace();
			}
			
		return nextFANum;
	}
	
    public int addFA(int pID,double FASum,int limit,Date startTime){//添加一笔定期存款
		//返回一个系统生成的定期存款笔数，即FANum
    	String sql;
		int re=0;
		String time=new SimpleDateFormat("yyyy-MM-dd").format(startTime);
		int FANum=getLastFANum(pID)+1;
		BankAccList bankacclist=new BankAccList();
		int CANum=bankacclist.getCANum(pID);
		
		sql="insert into facclist values (null,"+pID+","+FANum+","+FASum+","+limit+",'"+time+"')";
		re=data.update(sql);
		
		if(re==1 && bankacclist.setCABalance(CANum, bankacclist.getCABalance(CANum)-FASum)){
			return FANum;
		}
		return -1;
	}
    
    public double getFASum(int pID,int FANum){
		//参数账号FANum，返回存款额FASum
    	String sql;
		ResultSet rs=null;
		double FASum=0;
		
		sql="select * from facclist where pID="+pID+" and faNum="+FANum+" limit 1";
		rs=data.getResultSet(sql);
		try {
			while(rs.next()){
				FASum=rs.getDouble("faSum");
		}
			} catch (SQLException e) {
		
			e.printStackTrace();
			}
			
		return FASum;
	}
    
    public int getLimit(int pID,int FANum){
    	String sql;
		ResultSet rs=null;
		int limit=0;
		
		sql="select * from facclist where pID="+pID+" and faNum="+FANum+" limit 1";
		rs=data.getResultSet(sql);
		try {
			while(rs.next()){
				limit=rs.getInt("faLimit");
		}
			} catch (SQLException e) {
		
			e.printStackTrace();
			}
			
		return limit;
	}
    
    public boolean deleFA(int pID,int FANum){	
    	String sql;
		int re=0;
		
		sql="delete from facclist where pID="+pID+" and faNum="+FANum;
		re=data.update(sql);
		
		if(re==1){
			return true;
		}
		return false;
  	}
    
    public String getStartTime(int pID,int FANum){
    	String sql;
		ResultSet rs=null;
		String time="";
		
		sql="select * from facclist where pID="+pID+" and faNum="+FANum+" limit 1";
		rs=data.getResultSet(sql);
		try {
			while(rs.next()){
				time=rs.getString("faStartTime");
		}
			} catch (SQLException e) {
		
			e.printStackTrace();
			}
			
		return time;
    }
    
    public int getSize(){
    	String sql;
		ResultSet rs=null;
		int sum=0;
		
		sql="select count(*) as sum from facclist";
		rs=data.getResultSet(sql);
		try {
			if(rs.next()){
				sum=rs.getInt("sum");
		}
			} catch (SQLException e) {
		
			e.printStackTrace();
			}
			
		return sum;
    }
    
    public int getpSize(int p){
    	String sql;
		ResultSet rs=null;
		int sum=0;
		
		sql="select count(*) as sum from facclist where pID="+p;
		rs=data.getResultSet(sql);
		try {
			if(rs.next()){
				sum=rs.getInt("sum");
		}
			} catch (SQLException e) {
		
			e.printStackTrace();
			}
			
		return sum;
    }
    
}
